package com.voja.salonschedule.data;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Locale;

import com.voja.AndroidUtils.SQLiteHelper;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteStatement;
import android.util.Pair;

public class Event extends AppModel {
	
	public static final String DEFAULT_ORDER = "`date` DESC, `date_end` ASC";
	
	public static SimpleDateFormat dateTimeFormat  = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault());
	public static SimpleDateFormat dateTimeFormat2 = new SimpleDateFormat("dd.MMMM yyyy, HH:mm", Locale.getDefault());
	public static SimpleDateFormat dateFormat      = new SimpleDateFormat("dd.MMMM yyyy", Locale.getDefault());
	public static SimpleDateFormat dateFormat2     = new SimpleDateFormat("yyyy-MM-dd", Locale.getDefault());
	public static SimpleDateFormat dateFormat3     = new SimpleDateFormat("EE, dd.MM yyyy", Locale.getDefault());
	public static SimpleDateFormat dateFormat4     = new SimpleDateFormat("EEEE, dd.MMMM yyyy", Locale.getDefault());
	public static SimpleDateFormat timeFormat      = new SimpleDateFormat("HH:mm", Locale.getDefault());
	
	public Date date_start, date_end;
	
	String lastError = "";
	
	public class EventItem {
		public int  	id		   = 0;
		public Date		date_start = Calendar.getInstance().getTime();
		public Date     date_end   = Calendar.getInstance().getTime();
		public String	customer   = "";
		public String	service	   = "";
		public double	price	   = 0;
		public String   notes	   = "";
	};

	public Event(Context c) {
		super(c);
	}
	
	public ArrayList<Pair<String,String>> getLastPrices() {
		ArrayList<Pair<String,String>> list = new ArrayList<Pair<String,String>>();
		
		SQLiteHelper db = getConnection();
		db.open();
		String sql = "SELECT DISTINCT service, price FROM events WHERE price>0 ORDER BY `date` DESC";
		Cursor c = db.query(sql);
		while(c.moveToNext()) {
			list.add(Pair.create(c.getString(0), c.getString(1)));
		}
		c.close();
		db.close();
		
		return list;
	}
	
	public ArrayList<EventItem> loadEvents(Date date) {
		return loadEvents(date, false);
	}
	public ArrayList<EventItem> loadEvents(Date start, boolean wholeDay) {
		return loadEvents(start, wholeDay, DEFAULT_ORDER);
	}
	public ArrayList<EventItem> loadEvents(Date start, boolean wholeDay, String order_by) {
		Date end = new Date();
		end.setDate(start.getDate());
		end.setYear(start.getYear());
		end.setMonth(start.getMonth());
		
		if (wholeDay) {
			start.setHours(0);
			start.setMinutes(0);
			start.setSeconds(0);
		}
		end.setHours(23);
		end.setMinutes(59);
		end.setSeconds(59);
		
		return loadEvents(start, end, order_by);
	}
	
	public ArrayList<EventItem> loadEvents(Date start, Date end) {
		return loadEvents(start, end, DEFAULT_ORDER);
	}
	
	public ArrayList<EventItem> loadEvents(Date start, Date end, String order_by) {
		date_start = start;
		date_end   = end;
		
		ArrayList<EventItem> ret = new ArrayList<EventItem>();
		String s = dateTimeFormat.format(date_start);
		String e = dateTimeFormat.format(date_end);
		
		String fields = "id, date, date_end, customer, service";
		String sql = "SELECT " + fields + " " +
				     "FROM events "+
				     "WHERE `date` BETWEEN '"+s+"' AND '"+e+"' "+
				     "ORDER BY " + order_by;
		
		SQLiteHelper db = getConnection();
		db.open();
		Cursor c = db.query(sql);
		
		EventItem item;
		while (c.moveToNext()) {
			item = createEventFromCursor(c, fields);
			
			ret.add(item);
		}
		c.close();
		db.close();
		return ret;
	}
	
	public EventItem findById(int id) {
		EventItem event = new EventItem();
		
		String sql = "SELECT id, date, date_end, customer, service, price, notes FROM events WHERE id="+id;
		SQLiteHelper db = getConnection();
		db.open();
		Cursor c   = db.query(sql);
		if (c.moveToFirst()) {
			event = createEventFromCursor(c);
		}
		c.close();
		db.close();
		
		return event;
	}
	
	private EventItem createEventFromCursor(Cursor cursor) {
		return createEventFromCursor(cursor, "*");
	}
	
	private EventItem createEventFromCursor(Cursor cursor, String fields) {
		EventItem item = new EventItem();
		
		ArrayList<String> arrFields = new ArrayList<String>();
		if (fields.equals("*")) {
			arrFields.add("id");
			arrFields.add("date");
			arrFields.add("date_end");
			arrFields.add("customer");
			arrFields.add("service");
			arrFields.add("price");
			arrFields.add("notes");
		} else {
			String[] tmp = fields.split(",\\s*");
			for (int i=0; i<tmp.length; i++) {
				arrFields.add(tmp[i]);
			}
		}
		
		// ID
		if (arrFields.indexOf("id") >= 0) {
			item.id = cursor.getInt(cursor.getColumnIndex("id"));
		}
		
		// DATE
		if (arrFields.indexOf("date") >= 0) {
			try {
				item.date_start = dateTimeFormat.parse(cursor.getString(cursor.getColumnIndex("date")));
			} catch (ParseException ex) {
				item.date_start = null;
			}
		}
		
		// DATE END
		if (arrFields.indexOf("date_end") >= 0) {
			try {
				item.date_end = dateTimeFormat.parse(cursor.getString(cursor.getColumnIndex("date_end")));
			} catch (ParseException ex) {
				item.date_end = null;
			}
		}
		
		// CUSTOMER
		if (arrFields.indexOf("customer") >= 0) {
			item.customer = cursor.getString(cursor.getColumnIndex("customer"));
		}
		
		// SERVICE
		if (arrFields.indexOf("service") >= 0) {
			item.service = cursor.getString(cursor.getColumnIndex("service"));
		}
		
		// PRICE
		if (arrFields.indexOf("price") >= 0) {
			item.price = cursor.getDouble(cursor.getColumnIndex("price"));
		}
		
		// NOTES
		if (arrFields.indexOf("notes") >= 0) {
			item.notes = cursor.getString(cursor.getColumnIndex("notes"));
		}
		
		return item;
	}
	
	public boolean save(EventItem item) {
		boolean saved = true;
		
		String dbDate 	  = dateTimeFormat.format(item.date_start);
		String dbDateEnd  = dateTimeFormat.format(item.date_end);
		String dbCustomer = item.customer.replaceAll("'", "\\'");
		String dbService  = item.service.replaceAll("'", "\\'");
		double dbPrice	  = item.price;
		String dbNotes    = item.notes.replaceAll("'", "\\'");
		
		SQLiteStatement stmt;
		SQLiteHelper db = getConnection();
		db.open();
		if (item.id > 0) {
			stmt = prepare("UPDATE events SET "+
						"date = ?, "+
						"date_end = ?, " +
						"customer = ?, "+
						"service  = ?, "+
						"price  = ?, "+
						"notes  = ? "+
					"WHERE id = " + item.id);
			stmt.bindString(1, dbDate);
			stmt.bindString(2, dbDateEnd);
			stmt.bindString(3, dbCustomer);
			stmt.bindString(4, dbService);
			stmt.bindString(5, ""+dbPrice);
			stmt.bindString(6, dbNotes);
			stmt.execute();
		} else {
			stmt = prepare("INSERT INTO events(date, date_end, customer, service, price, notes) "+
						   "VALUES(?, ?, ?, ?, ?, ?)");
			stmt.bindString(1, dbDate);
			stmt.bindString(2, dbDateEnd);
			stmt.bindString(3, dbCustomer);
			stmt.bindString(4, dbService);
			stmt.bindString(5, ""+dbPrice);
			stmt.bindString(6, dbNotes);
			saved = stmt.executeInsert() > 0;
		}
		db.close();
		
		return saved;
	}
	
	public boolean delete(int id) {
		boolean deleted = false;
		
		SQLiteHelper db = getConnection();
		db.open();
		String sql = "DELETE FROM events WHERE id = " + id;
		if (db.execute(sql) > 0) {
			deleted = true;
		}
		db.close();
		
		return deleted;
	}
	
	public String getError() {
		return lastError;
	}
}
